import pandas as pd
import openpyxl

# 设置文件路径
file_path = r"e:\Projects\program-py\LEARN-数据分析\CASE-dashboard_epidemic\香港各区疫情数据_20250322.xlsx"

def debug_excel_file():
    """详细调试Excel文件内容"""
    print("=" * 80)
    print("Excel文件详细调试")
    print("=" * 80)
    
    try:
        # 方法1: 使用openpyxl查看工作表结构
        print("\n1. 使用openpyxl查看工作表:")
        wb = openpyxl.load_workbook(file_path, data_only=True)
        print(f"工作表数量: {len(wb.sheetnames)}")
        print(f"工作表名称: {wb.sheetnames}")
        
        for sheet_name in wb.sheetnames:
            sheet = wb[sheet_name]
            print(f"\n工作表 '{sheet_name}':")
            print(f"  最大行数: {sheet.max_row}")
            print(f"  最大列数: {sheet.max_column}")
            
            # 读取前10行数据
            print("  前10行数据:")
            for row_idx in range(1, min(11, sheet.max_row + 1)):
                row_data = []
                for col_idx in range(1, min(sheet.max_column + 1, 10)):  # 最多显示10列
                    cell_value = sheet.cell(row=row_idx, column=col_idx).value
                    row_data.append(str(cell_value) if cell_value is not None else "NULL")
                print(f"    行{row_idx:2d}: {row_data}")
        
        wb.close()
        
        # 方法2: 使用pandas读取
        print("\n2. 使用pandas读取:")
        df = pd.read_excel(file_path)
        print(f"DataFrame形状: {df.shape}")
        print(f"列名: {list(df.columns)}")
        print(f"数据类型:")
        for col in df.columns:
            print(f"  {col}: {df[col].dtype}")
        
        print("\n前10行数据:")
        print(df.head(10))
        
        print("\n数据统计:")
        print(df.describe(include='all'))
        
        # 检查是否有数值列
        numeric_cols = df.select_dtypes(include=['number']).columns
        print(f"\n数值型列: {list(numeric_cols)}")
        
        # 检查非数值列
        non_numeric_cols = df.select_dtypes(exclude=['number']).columns
        print(f"非数值型列: {list(non_numeric_cols)}")
        
        # 尝试转换字符串为数值
        for col in non_numeric_cols:
            if df[col].dtype == 'object':
                try:
                    converted = pd.to_numeric(df[col], errors='coerce')
                    if not converted.isna().all():
                        print(f"列 '{col}' 可以部分转换为数值 (非NaN值: {converted.notna().sum()})")
                        print(f"  转换后的前5个值: {converted.head().tolist()}")
                except:
                    pass
        
    except Exception as e:
        print(f"调试过程中出错: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    debug_excel_file()